package com.ltmall.api.dao.impl;

import com.ltmall.api.bean.*;
import com.ltmall.api.dao.IGoodsDAO;
import com.ltmall.api.util.PageUtil;
import com.wuwangkeji.homeflavor.comm.Path;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.math.BigInteger;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

public class GoodsDAO extends com.ltmall.api.dao.impl.BaseEntityDAO<Goods> implements
                                                                                         IGoodsDAO {

    private String[] tableNames;

    @Override
    @SuppressWarnings("unchecked")
    public List<ThGroupBuyingRules> groupBuyingActivityList(final PageUtil page,
                                                            final String directId,
                                                            final int groupType) {
        final Timestamp now = new Timestamp(System.currentTimeMillis());

        //【1】查询该小区下的所有团购商品
        final String HQL = "FROM ThDirectSupplyGoods DSG where DSG.directSupplyId=? and DSG.directType='1' ";
        List<ThDirectSupplyGoods> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    query.setParameter(0, new Integer(directId));
                    return query.list();
                }
            });

        //【2】
        //final String HQL1 = "FROM ThGroupBuyingRules gbr where gbr.state = 1 and gbr.startedAt<=? and gbr.endingAt>=? and gbr.groupType=? order by gbr.level desc  ";
        final String HQL1 = "FROM ThGroupBuyingRules gbr where gbr.state = 1 and gbr.startedAt<=? and gbr.endingAt>=? order by gbr.level desc ";
        List<ThGroupBuyingRules> list1 = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL1);
                    query.setParameter(0, now);
                    query.setParameter(1, now);
                    /* query.setParameter(2, groupType);*/
                    return query.list();
                }
            });

        //【3】求交集
        List<ThGroupBuyingRules> list2 = new ArrayList<ThGroupBuyingRules>();
        for (ThDirectSupplyGoods supplyGoods : list) {
            for (ThGroupBuyingRules rules : list1) {
                if (rules.getGoodsId() == supplyGoods.getGoodId()) {
                    list2.add(rules);
                }
            }
        }

        //Level排序
        Collections.sort(list2, new Comparator<ThGroupBuyingRules>() {
            public int compare(ThGroupBuyingRules o1, ThGroupBuyingRules o2) {
                if (o1.getLevel() < o2.getLevel()) {
                    return 1;
                }
                if (o1.getLevel() == o2.getLevel()) {
                    return 0;
                }
                return -1;
            }
        });

        return list2;
    }

    @Override
    public ThGroupBuyingActivity groupBuyingActivityGet(int groupType) {
        final Timestamp now = new Timestamp(System.currentTimeMillis());
        List list = this
            .getHibernateTemplate()
            .find(
                // "from ThGroupBuyingActivity tc where  tc.gmtStart<=? and tc.gmtEnd>=? and tc.groupType=? order by tc.id desc ",
                "from ThGroupBuyingActivity tc where  tc.gmtStart<=? and tc.gmtEnd>=?  order by tc.id desc ",
                new Object[] { now, now /*, groupType */});
        if (list.size() > 0) {
            ThGroupBuyingActivity activity = (ThGroupBuyingActivity) list.get(0);
            return activity;
        }
        return null;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<Integer> getShopID(final String province, final String city, final String county) {
        // TODO Auto-generated method stub

        final String SQL;
        if (county == null) {
            SQL = "SELECT SHOP.SHOP_ID"
                  + " from FLAVOR_SHOP SHOP left join  FLAVOR_BUSSINESS_INFO INFO  on INFO.COMPANY_ID = SHOP.BUSSINESS_ID "
                  + " where INFO.COMPANY_PROVINCE =? AND INFO.COMPANY_CITY =? ";
        } else {
            SQL = "SELECT SHOP.SHOP_ID"
                  + " from FLAVOR_SHOP SHOP left join  FLAVOR_BUSSINESS_INFO INFO  on INFO.COMPANY_ID = SHOP.BUSSINESS_ID "
                  + " where INFO.COMPANY_PROVINCE =? AND INFO.COMPANY_CITY =? AND INFO.COMPANY_COUNTY=?";
        }
        List<Integer> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, province);
                query.setParameter(1, city);
                if (county != null) {
                    query.setParameter(2, county);
                }
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public List<?> goodsList(final int shopID, final int type, final PageUtil page) {

        String hql;

        if (type == 3) {// 时令果蔬多几个参数
            hql = "SELECT GOODS.GOODS_ID,GOODS_PRICE,GOODS_PICTURE,GOODS_NUMBER,GOODS_SELLOUT,SHOP.SHOP_ID,SHOP.SHOP_NAME,GOODS.GOODS_PRICE_OLD,GOODS.GOODS_TITLE,GOODS.GOODS_LABEL,GOODS.GOODS_FORMAT"
                  + ",PPAY.PERCENT,PPAY.START_TIME,PPAY.END_TIME,PPAY.DELIVERY_TIME,GOODS.ACTIVITY_PICTURE"
                  + " from FLAVOR_GOODS GOODS left join FLAVOR_SHOP SHOP on GOODS.SHOP_ID = SHOP.SHOP_ID "
                  + " left join FLAVOR_PREPAY PPAY on GOODS.GOODS_ID = PPAY.GOODS_ID "
                  + " where GOODS.GOODS_TYPE=? AND GOODS.STATION=1 AND GOODS.SHOW =1 ";
        } else {
            hql = "SELECT GOODS.GOODS_ID,GOODS_PRICE,GOODS_PICTURE,GOODS_NUMBER,GOODS_SELLOUT,SHOP.SHOP_ID,SHOP.SHOP_NAME,GOODS.GOODS_PRICE_OLD,GOODS.GOODS_TITLE,GOODS.GOODS_LABEL,GOODS.GOODS_FORMAT"
                  + " from FLAVOR_GOODS GOODS left join FLAVOR_SHOP SHOP on GOODS.SHOP_ID = SHOP.SHOP_ID "
                  + " where GOODS.GOODS_TYPE=? AND GOODS.STATION=1 AND GOODS.SHOW =1 ";
        }

        if (shopID == 0) {
            hql = hql + "ORDER BY GOODS.GOODS_SELLOUT DESC,GOODS.GOODS_ID DESC";
        } else {
            hql = hql + "AND SHOP.SHOP_ID =? ORDER BY GOODS.GOODS_SELLOUT DESC,GOODS.GOODS_ID DESC";
        }

        final String SQL = hql;
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, type);
                if (shopID != 0) {
                    query.setParameter(1, shopID);
                }
                // 分页
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;

    }

    public List<?> goodsList(final int type, final PageUtil page) {
        System.out.println("goodsList begin: " + System.currentTimeMillis());
        String hql;
        //没改完
        if (type == 3) {// 时令果蔬多几个参数
            hql = "SELECT GOODS.FAKE_SELLOUT_SEED AS fakeSellOut, GOODS.GOODS_ID AS goodsID, GOODS_PRICE AS goodsPrice, GOODS_PICTURE AS goodsPic,"
                  + "? AS picPath, "
                  + "GOODS_NUMBER ,GOODS_SELLOUT AS sellNumber,SHOP.SHOP_ID,SHOP.SHOP_NAME,"
                  + "GOODS.GOODS_PRICE_OLD,GOODS.GOODS_TITLE AS goodsTitle,GOODS.GOODS_LABEL,GOODS.GOODS_FORMAT"
                  + ",PPAY.PERCENT,PPAY.START_TIME,PPAY.END_TIME,PPAY.DELIVERY_TIME,GOODS.ACTIVITY_PICTURE, "
                  + "fbi.COMPANY_PROVINCE, fbi.COMPANY_CITY, fbi.COMPANY_COUNTY"
                  + " from FLAVOR_GOODS GOODS left join FLAVOR_PREPAY PPAY on GOODS.GOODS_ID = PPAY.GOODS_ID, "
                  + "FLAVOR_SHOP SHOP ,flavor_bussiness_info fbi "
                  + " where GOODS.GOODS_TYPE=? AND GOODS.STATION=1 AND GOODS.SHOW =1 and "
                  + "shop.BUSSINESS_ID = fbi.COMPANY_ID and GOODS.SHOP_ID = SHOP.SHOP_ID ";

        } else {
            hql = "SELECT GOODS.FAKE_SELLOUT_SEED AS fakeSellOut, GOODS.GOODS_ID AS goodsID, GOODS_PRICE AS goodsPrice, GOODS_PICTURE AS goodsPic, "
                  + "? AS picPath, "
                  + "GOODS_NUMBER,GOODS_SELLOUT AS sellNumber,SHOP.SHOP_ID,SHOP.SHOP_NAME,GOODS.GOODS_PRICE_OLD,GOODS.GOODS_TITLE AS goodsTitle, "
                  + "GOODS.GOODS_LABEL,GOODS.GOODS_FORMAT, fbi.COMPANY_PROVINCE, fbi.COMPANY_CITY, fbi.COMPANY_COUNTY"
                  + " from FLAVOR_GOODS GOODS ,FLAVOR_SHOP SHOP ,flavor_bussiness_info fbi "
                  + " where GOODS.GOODS_TYPE=? AND GOODS.STATION=1 AND GOODS.SHOW =1 and shop.BUSSINESS_ID = fbi.COMPANY_ID and GOODS.SHOP_ID = SHOP.SHOP_ID"
                  + " ORDER BY GOODS.GOODS_SELLOUT DESC,GOODS.GOODS_ID DESC";
        }

        final String SQL = hql;
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
                query.setParameter(0, Path.nginxServerBusinessDir + Path.GOODS + File.separator);
                query.setParameter(1, type);
                // 分页
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        System.out.println("goodsList end: " + System.currentTimeMillis());
        return list;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<HotCity> fiveHotCity() {
        // TODO Auto-generated method stub
        final String HQL = "from HotCity oc where oc.isRecommend = 1 order by oc.weight desc";
        List<HotCity> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setFirstResult(0);
                query.setMaxResults(5);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public Goods goodsDetail(int goodsID) {
        List list = this.getHibernateTemplate().find("from Goods goods where goods.goodsID =?",
            new Object[] { goodsID });
        if (list.size() > 0) {
            return (Goods) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public GoodsServer goodsServer(int goodsID) {
        List list = this.getHibernateTemplate().find(
            "from GoodsServer server where server.goodsID =?", new Object[] { goodsID });
        if (list.size() > 0) {
            return (GoodsServer) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public Shop shopDetail(int shopID) {
        List list = this.getHibernateTemplate().find("from Shop s where s.shopID =?",
            new Object[] { shopID });
        if (list.size() > 0) {
            return (Shop) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public int getShopID(final int type, final String province, final String city,
                         final String county) {
        // TODO Auto-generated method stub
        String hql;
        int companyShop = 0;
        if (type == 4 || type == 5) {
            companyShop = 2;
        } else {
            companyShop = 1;
        }
        final int cShop = companyShop;

        if (cShop != 2) {// 特长礼品或者是工艺臻品
            hql = "SELECT SHOP.SHOP_ID"
                  + " from FLAVOR_SHOP SHOP left join FLAVOR_BUSSINESS_INFO INFO on SHOP.BUSSINESS_ID = INFO.COMPANY_ID "
                  + " where INFO.COMPANY_SHOP=? AND INFO.COMPANY_PROVINCE =? AND INFO.COMPANY_CITY =? and INFO.COMPANY_COUNTY =?";
        } else {
            hql = "SELECT SHOP.SHOP_ID"
                  + " from FLAVOR_SHOP SHOP left join FLAVOR_BUSSINESS_INFO INFO on SHOP.BUSSINESS_ID = INFO.COMPANY_ID "
                  + " where INFO.COMPANY_SHOP=? AND INFO.COMPANY_PROVINCE =? AND INFO.COMPANY_CITY =?";
        }

        final String SQL = hql;
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, cShop);
                query.setParameter(1, province);
                query.setParameter(2, city);

                if (cShop != 2 && county != null) {
                    query.setParameter(3, county);
                }
                return query.list();
            }
        });

        if (list.size() == 0) {
            return 0;
        } else {
            String sID = list.get(0).toString();
            return Integer.parseInt(sID);
        }
    }

    @SuppressWarnings("rawtypes")
    public List<?> brandGoods(final int brandID, final int type, final PageUtil page) {
        // TODO Auto-generated method stub
        String SQLPREFIX = "SELECT GOODS.GOODS_ID,GOODS.GOODS_PICTURE,GOODS_PRICE,GOODS.GOODS_TITLE,GOODS.GOODS_TYPE"
                           + " from FLAVOR_GOODS GOODS where GOODS.BRAND_ID = ? AND GOODS.STATION=1";

        /*
         * 0：综合 1：销量 2：价格由高到低 3：价格由低到高 4：评价
         */

        String SQLSUFFIX = "";
        switch (type) {
            case 1:// 销量
                SQLSUFFIX = " ORDER BY GOODS.GOODS_SELLOUT DESC";
                break;
            case 2:// 价格由低到高
                SQLSUFFIX = " ORDER BY GOODS.GOODS_PRICE ASC";
                break;
            case 3:// 加个由高到底
                SQLSUFFIX = " ORDER BY GOODS.GOODS_PRICE DESC";
                break;
            case 4:// 评价
                SQLSUFFIX = " ORDER BY GOODS.GOODS_PRICE ASC";
                break;
            default:// 综合
                SQLSUFFIX = " ORDER BY GOODS.GOODS_SELLOUT DESC";
                break;
        }

        final String SQL = SQLPREFIX + SQLSUFFIX;
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, brandID);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<Goods> topGoos(String province, String city, String county, final PageUtil page) {
        // TODO Auto-generated method stub
        final String HQL = "from Goods goods where goods.station =1 and goods.show =1 and goods.directSupplyShowType ='1' order by goods.sellOut desc";
        List<Goods> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                //query.setFirstResult(1);
                query.setMaxResults(10);
                query.setFirstResult(0);
                //                        query.setFirstResult(
                //                                        (page.getCur_page() - 1) * page.getPage_row())
                //                                        .setMaxResults(page.getPage_row());
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<Goods> popularGoodsGet(String province, String city, String county,
                                       final PageUtil page) {
        // TODO Auto-generated method stub
        final String HQL = "from Goods goods where goods.station =1 and goods.show =1 order by goods.sellOut desc";
        List<Goods> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<StoreHouse> getStoreHouse(final int userID) {
        final String HQL = "from StoreHouse house where house.bussinessID =(select house1.bussinessID from StoreHouse house1 where house1.userID = ?)";
        List<StoreHouse> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, userID);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public Shop shopDetailByBussiness(int bussinessID) {
        List list = this.getHibernateTemplate().find("from Shop s where s.bussinessID =?",
            new Object[] { bussinessID });
        if (list.size() > 0) {
            return (Shop) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public List<?> pingGoods(final List<Integer> shopList, final int goodsID, final PageUtil page) {
        // TODO Auto-generated method stub
        final String SQL;
        if (goodsID == 0) {
            SQL = "SELECT GOODS.GOODS_ID,GOODS_PRICE,GOODS_PICTURE,GOODS_NUMBER,GOODS_SELLOUT,SHOP.SHOP_ID,SHOP.SHOP_NAME,GOODS.GOODS_PRICE_OLD,GOODS.GOODS_TITLE,GOODS.GOODS_LABEL,GOODS.GOODS_FORMAT"
                  + " from FLAVOR_GOODS GOODS left join FLAVOR_SHOP SHOP on GOODS.SHOP_ID = SHOP.SHOP_ID"
                  + " where GOODS.STATION=1 AND SHOP.SHOP_ID IN (:alist) ";
        } else {
            SQL = "SELECT GOODS.GOODS_ID,GOODS_PRICE,GOODS_PICTURE,GOODS_NUMBER,GOODS_SELLOUT,SHOP.SHOP_ID,SHOP.SHOP_NAME,GOODS.GOODS_PRICE_OLD,GOODS.GOODS_TITLE,GOODS.GOODS_LABEL,GOODS.GOODS_FORMAT"
                  + " from FLAVOR_GOODS GOODS left join FLAVOR_SHOP SHOP on GOODS.SHOP_ID = SHOP.SHOP_ID"
                  + " where GOODS.STATION=1 AND GOODS.GOODS_LOGISTIC = (:goods_id) AND SHOP.SHOP_ID IN (:alist) ";
        }
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameterList("alist", shopList);
                if (goodsID != 0) {
                    query.setParameter("goods_id", goodsID);
                }
                // 分页
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public GoodsMore goodsMore(int goodsID) {
        List list = this.getHibernateTemplate().find("from GoodsMore m where m.goodsID =?",
            new Object[] { goodsID });
        if (list.size() > 0) {
            return (GoodsMore) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public GoodsStory story(int goodsID) {
        List list = this.getHibernateTemplate().find(
            "from GoodsStory story where story.goodsID =?", new Object[] { goodsID });
        if (list.size() > 0) {
            return (GoodsStory) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public List<?> latestComment(final int goodsID) {
        // TODO Auto-generated method stub
        //		final long  intervalTime = 2*24*60*60*1000l;//两日之后再显示出来
        final String SQL = "SELECT INFO.USER_ID,INFO.NAME,INFO.PHOTO,COMME.CONTENT,COMME.OPTIME,COMME.RATING_GOODS,ACC.PHONE"
                           + " from FLAVOR_GOODS_COMMENT COMME left join FLAVOR_CLIENT_INFO INFO on COMME.USER_ID = INFO.USER_ID"
                           + " left join FLAVOR_CLIENT_ACCOUNT ACC on COMME.USER_ID = ACC.USER_ID"
                           + " where COMME.GOODS_ID = ? order by COMME.OPTIME DESC ";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, goodsID);
                //						query.setParameter(1, System.currentTimeMillis()-intervalTime);
                // 分页
                query.setFirstResult(0).setMaxResults(1);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public List<?> commentList(final int goodsID, final PageUtil page) {
        // TODO Auto-generated method stub
        //		final long  intervalTime = 2*24*60*60*1000l;//两日之后再显示出来
        final String SQL = "SELECT INFO.USER_ID,INFO.NAME,INFO.PHOTO,COMME.CONTENT,COMME.OPTIME,COMME.RATING_GOODS,ACC.PHONE"
                           + " from FLAVOR_GOODS_COMMENT COMME left join FLAVOR_CLIENT_INFO INFO on COMME.USER_ID = INFO.USER_ID"
                           + " left join FLAVOR_CLIENT_ACCOUNT ACC on COMME.USER_ID = ACC.USER_ID"
                           + " where COMME.GOODS_ID = ? order by COMME.OPTIME DESC";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, goodsID);
                //						query.setParameter(1, System.currentTimeMillis()-intervalTime);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public List<ADS> ads(final int type, final int number) {
        // TODO Auto-generated method stub
        final String HQL = "from ADS ads where ads.type = ? order by ads.weight desc ";
        @SuppressWarnings("unchecked")
        List<ADS> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, type);
                // 分页
                query.setFirstResult(0).setMaxResults(number);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public List<BussinessADS> shopADS(final int number, final int bussinessID) {
        // TODO Auto-generated method stub
        final String HQL = "from BussinessADS ads where ads.station=1 and ads.bussinessID = ? order by ads.opTime desc ";
        @SuppressWarnings("unchecked")
        List<BussinessADS> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    query.setParameter(0, bussinessID);
                    // 分页
                    query.setFirstResult(0).setMaxResults(number);
                    return query.list();
                }
            });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public int getBussinessID(String province, String city, String county, int type) {
        // TODO Auto-generated method stub

        String hql;
        Object param[];
        if (county == null) {
            hql = "from  BussinessInfo info where info.companyProvince =? and info.companyCity=? and info.companyShop = ?";
            param = new Object[] { province, city, type };
        } else {
            hql = "from  BussinessInfo info where info.companyProvince =? and info.companyCity=? and info.companyCounty = ? and info.companyShop=?";
            param = new Object[] { province, city, county, type };
        }
        List list = this.getHibernateTemplate().find(hql, param);
        if (list.size() > 0) {
            BussinessInfo info = (BussinessInfo) list.get(0);
            return info.getCompanyID();
        }
        return 0;
    }

    public void updateGoodsNumber(int goodsID, int deleteNumber) {
        // TODO Auto-generated method stub
        String hql = "update Goods goods set goods.goodsNumber = (goods.goodsNumber-?)"
                     + "where goods.goodsID = ?";
        if (deleteNumber > 0) {
            hql += "and goods.goodsNumber>0";
        }
        try {
            Query query = getSession().createQuery(hql);
            query.setParameter(0, deleteNumber);
            query.setParameter(1, goodsID);
            query.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public boolean reduceSkuStock(int skuId, int number) {
        final String SQL = "update th_sku set GOODS_STOCK = GOODS_STOCK - ? where ID = ?";
        Session session = getSession();
        Query query = session.createSQLQuery(SQL);
        query.setParameter(0, number);
        query.setParameter(1, skuId);
        int rows = query.executeUpdate();
        if (rows == 1) {
            return true;
        } else {
            return false;
        }
    }

    public void updateGoodsStock(final int goodsID) {
        final String SQL = "update flavor_goods fg set fg.GOODS_NUMBER = "
                           + "(SELECT ts.GOODS_STOCK FROM th_sku ts WHERE ts.GOODS_ID = ? and ts.HIDDEN = 0 LIMIT 0, 1) "
                           + "WHERE fg.GOODS_ID = ?";
        Session session = getSession();
        Query query = session.createSQLQuery(SQL);
        query.setParameter(0, goodsID);
        query.setParameter(1, goodsID);
        query.executeUpdate();
    }

    public void updateSellOut(int goodsID, int sellNumber) {
        // TODO Auto-generated method stub
        String hql = "update Goods goods set goods.sellOut = (goods.sellOut+?)"
                     + "where goods.goodsID = ?";
        try {
            Query query = getSession().createQuery(hql);
            query.setParameter(0, sellNumber);
            query.setParameter(1, goodsID);
            query.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void updateSkuSellOut(int skuID, int sellNumber) {
        // TODO Auto-generated method stub
        String hql = "update ThSku sku set sku.sellout = (sku.sellout+?)" + "where sku.ID = ?";
        try {
            Query query = getSession().createQuery(hql);
            query.setParameter(0, sellNumber);
            query.setParameter(1, skuID);
            query.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThSku> getPrice(final int goodsID) {
        // TODO Auto-generated method stub
        final String HQL = "from ThSku price where price.goodsID = ?";
        List<ThSku> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, goodsID);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public Activity runningActivtiy() {
        // TODO Auto-generated method stub
        final long cTime = System.currentTimeMillis();// 当前时间
        final String HQL = "from Activity activity where activity.startTime<=? and activity.endTime>?";
        @SuppressWarnings("unchecked")
        List<Activity> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, cTime);
                query.setParameter(1, cTime);
                return query.list();
            }
        });
        if (list.size() == 0) {
            return null;
        }
        return list.get(0);
    }

    @SuppressWarnings("rawtypes")
    public Activity goingRunningActivtiy() {
        // TODO Auto-generated method stub
        final long cTime = System.currentTimeMillis();// 当前时间
        final String HQL = "from Activity activity where activity.startTime>? order by activity.startTime asc";
        @SuppressWarnings("unchecked")
        List<Activity> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, cTime);
                return query.list();
            }
        });
        if (list.size() == 0) {
            return null;
        }
        return list.get(0);
    }

    @SuppressWarnings("rawtypes")
    public Brand getBrand(int brandID) {
        List list = this.getHibernateTemplate().find("from Brand brand where brand.brandID =?",
            new Object[] { brandID });
        if (list.size() > 0) {
            return (Brand) list.get(0);
        }
        return null;
    }

    public boolean remind(int goodsID, int type) {
        // TODO Auto-generated method stub
        String hql = null;
        switch (type) {
            case 0:// 取消收藏
                int remindNumber = remindNumber(goodsID);
                if (remindNumber == 0) {// 在设置数量=0的情况下
                    return true;
                } else {
                    hql = "update PrePay pp set pp.remindNumber = pp.remindNumber-1 where pp.goodsID = ?";
                }
                break;
            case 1:// 添加收藏
                hql = "update PrePay pp set pp.remindNumber = pp.remindNumber+1 where pp.goodsID = ?";
                break;
        }
        try {
            Query query = getSession().createQuery(hql);
            query.setParameter(0, goodsID);
            int number = query.executeUpdate();
            if (number == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @SuppressWarnings("rawtypes")
    public int remindNumber(int goodsID) {
        List list = this.getHibernateTemplate().find("from PrePay pp where pp.goodsID =?",
            new Object[] { goodsID });
        if (list.size() > 0) {
            PrePay pp = (PrePay) list.get(0);
            return pp.getRemindNumber();
        }
        return 0;
    }

    @SuppressWarnings("rawtypes")
    public int isCollection(int userID, int goodsID) {
        List list = this.getHibernateTemplate().find(
            "from Collection co where co.goodsID =? and co.userID = ?",
            new Object[] { goodsID, userID });
        if (list.size() > 0) {
            return 1;
        }
        return 0;
    }

    @SuppressWarnings("rawtypes")
    public AutoReply autoReply(int shopID) {
        List list = this.getHibernateTemplate().find("from AutoReply ar where ar.shopID =?",
            new Object[] { shopID });
        if (list.size() > 0) {
            return (AutoReply) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public int commentNumber(int goodsID) {
        List list = this.getHibernateTemplate().find("from Comment co where co.goodsID =?",
            new Object[] { goodsID });
        return list.size();
    }

    @SuppressWarnings("rawtypes")
    public List<?> goodsRating(final int goodsID) {
        // TODO Auto-generated method stub
        final String SQL = "SELECT AVG(CO.RATING_GOODS),AVG(CO.RATING_SERVICE),AVG(CO.RATING_SPEED)"
                           + " from FLAVOR_GOODS_COMMENT CO" + " where CO.GOODS_ID = ? ";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, goodsID);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public ThSku getGoodsPrice(int goodsID, String format) {
        // TODO Auto-generated method stub
        List list = this.getHibernateTemplate()
            .find("from ThSku gp where gp.goodsID =? and gp.format = ?",
                new Object[] { goodsID, format });
        if (list.size() > 0) {
            return (ThSku) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public ThSku getGoodsPrice(int skuId) {
        final String HQL = "from ThSku sku where sku.ID = " + skuId;
        List list = this.getHibernateTemplate().find(HQL);
        if (list.size() > 0) {
            return (ThSku) list.get(0);
        }
        return null;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<Broadcast> castList() {
        // TODO Auto-generated method stub
        final String HQL = "from Broadcast bro";
        List<Broadcast> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setFirstResult(0);
                query.setMaxResults(5);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public ModelPicture getModelPicture() {
        // TODO Auto-generated method stub
        final String HQL = "from ModelPicture mp";
        @SuppressWarnings("unchecked")
        List<ModelPicture> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    return query.list();
                }
            });
        if (list.size() == 0) {
            return null;
        }
        return list.get(0);
    }

    @SuppressWarnings("rawtypes")
    public PrePay getPrepay(int goodsID) {
        // TODO Auto-generated method stub
        List list = this.getHibernateTemplate().find("from PrePay pp where pp.goodsID =?",
            new Object[] { goodsID });
        if (list.size() > 0) {
            return (PrePay) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public ActivityPrice getActivityPrice(int activityID, int goodsID, String format) {
        List list = this.getHibernateTemplate().find(
            "from ActivityPrice ap where ap.activityID =? and ap.goodsID = ? and ap.format = ?",
            new Object[] { activityID, goodsID, format });
        System.out.println("activityID" + activityID + ";goodsID" + goodsID + ";format" + format);
        if (list.size() > 0) {
            return (ActivityPrice) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public BussinessInfo getBussinessInfo(int bussinessID) {
        List list = this.getHibernateTemplate().find("from BussinessInfo bi where bi.companyID =?",
            new Object[] { bussinessID });
        if (list.size() > 0) {
            return (BussinessInfo) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public int goodComment(int goodsID) {
        // TODO Auto-generated method stub
        float goodsStandard = 9.0f;
        List list = this
            .getHibernateTemplate()
            .find(
                "from Comment co where co.goodsID = ? and (co.goodsRating+ co.serviceRating+co.speedRating)>=?",
                new Object[] { goodsID, goodsStandard });
        return list.size();
    }

    @SuppressWarnings("rawtypes")
    public ActivityPrice getActivityPrice(int goodsID, String format) {
        long cTime = System.currentTimeMillis();
        List list = this
            .getHibernateTemplate()
            .find(
                "from ActivityPrice ap where ap.goodsID =? and ap.format = ? and ap.startTime<=? and ap.endTime>?",
                new Object[] { goodsID, format, cTime, cTime });
        if (list.size() > 0) {
            return (ActivityPrice) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public ThSku skuGet(int skuID) {
        List list = this.getHibernateTemplate().find("from ThSku ap where ap.ID =? ",
            new Object[] { skuID });
        if (list.size() > 0) {
            return (ThSku) list.get(0);
        }
        return null;
    }

    public List<HotCity> recommendCityList(final String city, final String county) {
        // TODO Auto-generated method stub
        final String HQL = "from HotCity hc where hc.city = ? and hc.county != ?";
        List<HotCity> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, city);
                query.setParameter(1, county);
                query.setFirstResult(0);
                return query.list();
            }
        });
        return list;
    }

    public List<?> recommendCityListGet(final String city, final String county) {
        final String SQL = "SELECT bu.company_province,bu.company_city,bu.company_county,shop.SHOP_POSTER,shop.ENGLISH_CITY_NAME from flavor_shop shop"
                           + " LEFT join flavor_bussiness_info bu on shop.BUSSINESS_ID = bu.COMPANY_ID"
                           + " where bu.COMPANY_SHOP = 1 and bu.COMPANY_CITY = ? and bu.COMPANY_COUNTY != ?";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, city);
                query.setParameter(1, county);
                return query.list();
            }
        });
        return list;
    }

    public List<ThCategories> getCategoryList(final PageUtil page) {
        // TODO Auto-generated method stub
        final String HQL = "from ThCategories c where c.isShow = '1' AND c.parentId = '0' order by c.orderBy DESC";
        @SuppressWarnings("unchecked")
        List<ThCategories> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                        .setMaxResults(page.getNumPerPage());
                    return query.list();
                }
            });
        return list;
    }

    public List<ThCateGroup> cateGroupGet() {
        String hql = "from ThCateGroup cg where cg.isShow = 1 order by cg.orderBy DESC, cg.id DESC";
        List list = this.getHibernateTemplate().find(hql);
        if (list.size() > 0) {
            return list;
        }
        return null;
    }

    public List<?> categoriesGet(final int groupId, final int shopId) {
        String SQL = null;
        System.out.println("categoryTreeGetDao begin:" + System.currentTimeMillis());
        if (shopId != 0) {
            SQL = "SELECT tc.id, tc.parent_id, tc.cate_name, tc.cate_img, tc.keywords, tc.cate_desc, tc.thumbnail, tc.redirect_to, tc.masking_layer, tc.masking_layer_posi, tsc.photo"
                  + " from th_categories tc LEFT JOIN th_cate_group_categories tcgc ON tc.id = tcgc.cate_id LEFT JOIN th_shop_cate tsc ON tc.id = tsc.cate_id "
                  + "where tc.is_show = 1 AND tc.parent_id = 0 AND tc.id != 4 AND tc.id != 5 AND tcgc.group_id = ? AND tsc.shop_id = ? ORDER BY tc.order_by DESC, tc.id DESC";
        } else {
            SQL = "SELECT tc.id, tc.parent_id, tc.cate_name, tc.cate_img, tc.keywords, tc.cate_desc, tc.thumbnail, tc.redirect_to, tc.masking_layer, tc.masking_layer_posi"
                  + " from th_categories tc LEFT JOIN th_cate_group_categories tcgc ON tc.id = tcgc.cate_id "
                  + "where tc.is_show = 1 AND tc.parent_id = 0 AND tc.id != 4 AND tc.id != 5 AND tcgc.group_id = ? ORDER BY tc.order_by DESC, tc.id DESC";
        }

        final String finalSQL = SQL;

        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(finalSQL);
                query.setParameter(0, groupId);
                if (shopId != 0) {
                    query.setParameter(1, shopId);
                }
                return query.list();
            }
        });
        System.out.println("categoryTreeGet end:" + System.currentTimeMillis());
        if (list.size() > 0) {
            return list;
        }
        return null;
    }

    public ThShopCate shopCateGet(int cateId, int shopId) {
        String hql = "from ThShopCate sc where sc.shopId = ? and sc.cateId = ?";
        List list = this.getHibernateTemplate().find(hql, new Object[] { shopId, cateId });
        if (list.size() > 0) {
            return (ThShopCate) list.get(0);
        }
        return null;
    }

    public BussinessInfo getBusinessByCounty(String countyID) {
        // TODO Auto-generated method stub
        String hql = "from BussinessInfo bi where bi.companyCounty=?";
        List list = this.getHibernateTemplate().find(hql, new Object[] { countyID });
        if (list.size() > 0) {
            return (BussinessInfo) list.get(0);
        }
        return null;
    }

    public List<ThCategories> getCateListByParentAndBussiness(final int parentID,
                                                              final int bussinessID,
                                                              final PageUtil page) {
        // TODO Auto-generated method stub
        final String HQL = "FROM ThCategories tc LEFT JOIN ThBusinessCate tbc on tc.id = tbc.cateId where tc.parentId = ? AND tc.isShow = '1' AND tbc.businessId = ? order by tc.orderBy DESC";
        @SuppressWarnings("unchecked")
        List<ThCategories> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    query.setParameter(0, bussinessID);
                    query.setParameter(1, parentID);
                    query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                        .setMaxResults(page.getNumPerPage());
                    return query.list();
                }
            });
        return list;
    }

    // 获取团购列表
    @SuppressWarnings("rawtypes")
    public List<ThGroupBuyingRules> groupBuyingGroupRulesGet(final PageUtil page) {
        final Timestamp now = new Timestamp(System.currentTimeMillis());
        //TODO 要不要加上时间的判断
        final String HQL = "FROM ThGroupBuyingRules gbr where gbr.state = 1 and gbr.startedAt<=? and gbr.endingAt>= ?";
        @SuppressWarnings("unchecked")
        List<ThGroupBuyingRules> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    query.setParameter(0, now);
                    query.setParameter(1, now);
                    query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                        .setMaxResults(page.getNumPerPage());
                    return query.list();
                }
            });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public ThGroupBuyingRules groupBuyingRuleGet(final int groupRulesId) {
        final String HQL = "FROM ThGroupBuyingRules gbr where gbr.id = ? ";
        @SuppressWarnings("unchecked")
        List<ThGroupBuyingRules> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    query.setParameter(0, groupRulesId);
                    return query.list();
                }
            });
        if (list.size() == 1) {
            return list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public List<ThGroupBuying> groupBuyingGroupGet(final int groupRulesId, final int num) {
        String HQL = "FROM ThGroupBuying gb where gb.groupBuyingRulesId = ? and gb.state in (0,1)";
        if (num > 0) {
            HQL = "FROM ThGroupBuying gb where gb.groupBuyingRulesId = ? and gb.state = 1";
        } else {
            HQL = "FROM ThGroupBuying gb where gb.groupBuyingRulesId = ? and gb.state in (0,1)";
        }
        final String hql = HQL + "order by gb.createdAt DESC";
        @SuppressWarnings("unchecked")
        List<ThGroupBuying> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(hql);
                    query.setParameter(0, groupRulesId);
                    if (num > 0) {
                        query.setFirstResult(0).setMaxResults(num);
                    }
                    return query.list();
                }
            });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public ThGroupBuying groupBuyingGet(final int groupId) {
        final String HQL = "FROM ThGroupBuying gbr where gbr.id = ? ";
        @SuppressWarnings("unchecked")
        List<ThGroupBuying> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    query.setParameter(0, groupId);
                    return query.list();
                }
            });
        if (list.size() == 1) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public ThGroupBuying groupBuyingGetByRulesId(final int groupRulesId) {
        final String HQL = "FROM ThGroupBuying gbr where gbr.groupBuyingRulesId = ? ";
        @SuppressWarnings("unchecked")
        List<ThGroupBuying> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    query.setParameter(0, groupRulesId);
                    return query.list();
                }
            });
        if (list.size() >= 1) {
            return list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public ThGroupBuying groupBuyingGet(final int groupRulesId, final int userId) {
        final String HQL = "FROM ThGroupBuying gbr where gbr.groupBuyingRulesId = ? and gbr.groupCaptain = ? and gbr.state in (0,1)";
        @SuppressWarnings("unchecked")
        List<ThGroupBuying> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    query.setParameter(0, groupRulesId);
                    query.setParameter(1, userId);
                    return query.list();
                }
            });
        if (list.size() >= 1) {
            return list.get(0);
        }
        return null;
    }

    public boolean groupBuyingUpdate(ThGroupBuying group) {
        try {
            getHibernateTemplate().update(group);
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    // 获取已经参团的用户列表
    @SuppressWarnings("rawtypes")
    public List<?> groupByingHistoryGet(final int groupId) {
        final String SQL = "SELECT OD.USER_ID,CONTENT.OPTIME,OD.ORDER_ID,OD.FEE,CONTENT.GOODS_TITLE,CONTENT.SKU_ID,CONTENT.DIRECT_ID,CONTENT.AMOUNT"
                           + " from FLAVOR_ORDER_CONTENT CONTENT LEFT join FLAVOR_ORDER OD ON CONTENT.ORDER_ID = OD.ORDER_ID "
                           + " where CONTENT.GROUP_ID = ? and CONTENT.ACTIVITY_SUCCESS = 1 AND CONTENT.ACTIVITY_NAME = 'ThGroupBuyingRules' "
                           + " order by CONTENT.OPTIME DESC";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, groupId);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public List<?> topicsGet(final int shopId, final int categoryId) {
        String SQL = null;
        if (shopId == 0) {
            SQL = "SELECT tt.id, tt.title, tt.subtitle, tt.description, tt.state, tt.image, tt.start_at, tt.end_at"
                  + " FROM th_topics tt LEFT JOIN th_topic_categories ttc ON tt.id = ttc.topic_id "
                  + "WHERE ttc.cate_id = ? AND (UNIX_TIMESTAMP(tt.start_at) * 1000) <= ? AND (UNIX_TIMESTAMP(tt.end_at) * 1000) >= ? AND tt.hide = 0 ORDER BY tt.start_at DESC";

        } else {
            SQL = "SELECT tt.id, tt.title, tt.subtitle, tt.description, tt.state, tt.image, tt.start_at, tt.end_at"
                  + " FROM th_topics tt LEFT JOIN th_topic_categories ttc ON tt.id = ttc.topic_id "
                  + "WHERE ttc.cate_id = ? AND (UNIX_TIMESTAMP(tt.start_at) * 1000) <= ? AND (UNIX_TIMESTAMP(tt.end_at) * 1000) >= ? AND tt.shop_id = ? AND tt.hide = 0 ORDER BY tt.start_at DESC";
        }

        final String finalSQL = SQL;
        final long currentTime = System.currentTimeMillis();
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(finalSQL);
                query.setParameter(0, categoryId);
                query.setParameter(1, currentTime);
                query.setParameter(2, currentTime);
                if (shopId != 0) {
                    query.setParameter(3, shopId);
                }
                return query.list();
            }
        });
        return list;
    }

    public List<?> getTopics(final String topicsType, final int shopId, final String state,
                             final String startTime, final String endTime, final PageUtil page) {
        String hql = "FROM ThTopics WHERE shopId = :shopId and hide = 0";

        if (!topicsType.equals("-1")) {
            hql += " and topicsType = :topicsType ";
        }

        if (!state.equals("-1")) {
            hql += " and state = :state ";
        }

        if (!startTime.equals("-1")) {
            hql += " and UNIX_TIMESTAMP(start_at) >= :startTime ";
        }

        if (!endTime.equals("-1")) {
            hql += "and UNIX_TIMESTAMP(end_at) <= :endTime ";
        }

        final String HQL = hql + " order by level desc";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List<?>>() {

            public List<?> doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setString("shopId", String.valueOf(shopId));

                if (!topicsType.equals("-1")) {
                    query.setString("topicsType", topicsType);
                }

                if (!state.equals("-1")) {
                    query.setString("state", state);
                }

                if (!startTime.equals("-1")) {
                    query.setString("startTime", startTime);
                }

                if (!endTime.equals("-1")) {
                    query.setString("endTime", endTime);
                }

                query.setMaxResults(page.getNumPerPage()).setFirstResult(
                    (page.getCur_page() - 1) * page.getNumPerPage());
                return query.list();
            }

        });

        return list;
    }

    public boolean isShowTopic(int topicId) {
        final String finalHql = "SELECT count(*) FROM th_topic_goods ttg, flavor_goods fg "
                                + "where ttg.goods_id = fg.goods_id and ttg.id = ?";
        Query query = this.getSession().createSQLQuery(finalHql);
        query.setParameter(0, topicId);
        Integer number = ((BigInteger) query.uniqueResult()).intValue();
        if (number == 0) {
            return false;
        }
        return true;
    }

    @SuppressWarnings("rawtypes")
    public List<?> topicGoodsGet(final int topicId, final int shopId) {
        final String SQL = "SELECT GOODS.GOODS_ID, GOODS_PRICE, GOODS_PICTURE, GOODS_NUMBER, GOODS_SELLOUT, SHOP.SHOP_ID, SHOP.SHOP_NAME, "
                           + "GOODS.GOODS_PRICE_OLD, GOODS.GOODS_TITLE, GOODS.GOODS_LABEL, GOODS.GOODS_FORMAT, PPAY.PERCENT, PPAY.START_TIME, PPAY.END_TIME,PPAY.DELIVERY_TIME"
                           + " from TH_TOPIC_GOODS TG left join FLAVOR_GOODS GOODS on GOODS.GOODS_ID = TG.GOODS_ID left join FLAVOR_SHOP SHOP on GOODS.SHOP_ID = SHOP.SHOP_ID "
                           + " left join FLAVOR_PREPAY PPAY on GOODS.GOODS_ID = PPAY.GOODS_ID "
                           + " where GOODS.STATION = 1 AND GOODS.SHOW = 1 AND GOODS.SHOP_ID = ? AND TG.TOPIC_ID = ? ORDER BY GOODS.GOODS_SELLOUT DESC,GOODS.GOODS_ID DESC ";

        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, shopId);
                query.setParameter(1, topicId);
                return query.list();
            }
        });
        return list;
    }

    public Object topicGet(final int topicId) {
        final String SQL = "SELECT tt.title, tt.subtitle, tt.description, tt.image, fs.shop_id, fs.shop_logo, ttc.id"
                           + " FROM th_topics tt LEFT JOIN th_topic_categories ttc ON tt.id = ttc.topic_id LEFT JOIN flavor_shop fs ON tt.shop_id = fs.shop_id "
                           + "WHERE tt.id = ? ";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, topicId);
                return query.list();
            }
        });

        if (list.size() == 1) {
            return list.get(0);
        }
        return 0;
    }

    public float topicGoodsFloorPrice(final int topicId) {
        final String SQL = "SELECT MIN(g.goods_price)"
                           + " FROM flavor_goods g RIGHT JOIN th_topic_goods tg ON g.goods_id = tg.goods_id "
                           + "WHERE tg.topic_id = ?";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, topicId);
                return query.list();
            }
        });

        if (list.size() == 1 && list.get(0) != null) {
            return Float.parseFloat(list.get(0).toString());
        }
        return 0;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<Goods> wholesaleGoodsGet(String goodsType, String province, String city,
                                         String county, int type, final PageUtil page) {
        // TODO Auto-generated method stub
        String sql = "from Goods goods where goods.station = 1 and goods.show = 1 and goods.goodsType = ";
        sql += goodsType + " ";

        /*
         * 0：综合 1：销量 2：价格由高到低 3：价格由低到高 4：评价
         */

        String str = "";
        switch (type) {
            case 1:// 销量
                str = " order by goods.sellOut desc";
                break;
            case 2:// 价格由低到高
                str = " order by goods.goodsPrice asc";
                break;
            case 3:// 加个由高到底
                str = " order by goods.goodsPrice desc";
                break;
            case 4:// 评价
                str = " order by goods.goodsPrice asc";
                break;
            default:// 其他
                str = " order by goods.sellOut desc";
                break;
        }

        final String SQL = sql + str;
        List<Goods> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(SQL);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    public List<ThBanners> bannerListGet() {
        try {
            List list = this.getHibernateTemplate().find("from ThBanners tb ORDER BY tb.id ASC");
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<?> cateListInShop(final int shopId) {
        String SQL = null;
        if (shopId == 0) {
            SQL = "SELECT ttc.cate_id, COUNT(tt.id)"
                  + " FROM th_topic_categories ttc LEFT JOIN th_topics tt ON tt.id = ttc.topic_id "
                  + "WHERE (UNIX_TIMESTAMP(tt.start_at) * 1000) <= ? AND (UNIX_TIMESTAMP(tt.end_at) * 1000) >= ? AND tt.hide = 0 GROUP BY ttc.cate_id";
        } else {
            SQL = "SELECT ttc.cate_id, COUNT(tt.id)"
                  + " FROM th_topic_categories ttc LEFT JOIN th_topics tt ON tt.id = ttc.topic_id "
                  + "WHERE (UNIX_TIMESTAMP(tt.start_at) * 1000) <= ? AND (UNIX_TIMESTAMP(tt.end_at) * 1000) >= ? AND tt.hide = 0 AND tt.shop_id = ? GROUP BY ttc.cate_id";
        }

        final String finalSQL = SQL;
        final long currentTime = System.currentTimeMillis();

        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(finalSQL);
                query.setParameter(0, currentTime);
                query.setParameter(1, currentTime);
                if (shopId != 0) {
                    query.setParameter(2, shopId);
                }
                return query.list();
            }
        });
        return list;
    }

    public boolean isCateHavingGoods(int cateId) {
        final String SQL = "SELECT COUNT(*) FROM flavor_goods fg WHERE fg.goods_type = ?";
        Query query = getSession().createSQLQuery(SQL);
        query.setParameter(0, cateId);
        int numberOfGoods = ((BigInteger) query.uniqueResult()).intValue();
        if (numberOfGoods == 0) {
            return false;
        }
        return true;
    }

    public List<?> getGoodsByCateIdAndShopId(final int cateId, int shopId, final PageUtil page) {
        final List<?> objectArray = getAddressByShopId(shopId);
        Object[] addrObject = (Object[]) objectArray.get(0);
        final String province = (String) addrObject[0];
        final String city = (String) addrObject[1];
        final String country = (String) addrObject[2];

        final String SQL = "SELECT fg.goods_id FROM flavor_shop fs, flavor_goods fg, flavor_bussiness_info fbi "
                           + "WHERE fs.SHOP_ID = fg.SHOP_ID AND fs.BUSSINESS_ID = fbi.COMPANY_ID AND fg.GOODS_TYPE = ? AND fg.STATION = 1 "
                           + "ORDER BY CASE fbi.COMPANY_PROVINCE WHEN ? then 0 ELSE 1 END, "
                           + "CASE fbi.COMPANY_CITY WHEN ? THEN 0 ELSE 1 END, "
                           + "CASE fbi.COMPANY_COUNTY WHEN ? THEN 0 ELSE 1 END, fg.GOODS_SELLOUT DESC";

        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List<?>>() {
            public List<?> doInHibernate(Session session) throws HibernateException, SQLException {
                // TODO Auto-generated method stub
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, cateId);
                query.setParameter(1, province);
                query.setParameter(2, city);
                query.setParameter(3, country);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());

                return query.list();
            }
        });
        return list;
    }

    public List<?> getAddressByShopId(final int shopId) {
        final String SQL = "select fbi.COMPANY_PROVINCE, fbi.COMPANY_CITY, fbi.COMPANY_COUNTY  "
                           + "FROM flavor_shop fs LEFT JOIN flavor_bussiness_info fbi ON fs.BUSSINESS_ID = fbi.COMPANY_ID "
                           + "WHERE fs.shop_id = ?";

        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List<?>>() {
            public List<?> doInHibernate(Session session) throws HibernateException, SQLException {
                // TODO Auto-generated method stub
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, shopId);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("unchecked")
    public List<Integer> getShopIdByAreaName(final String province, final String city,
                                             final String country) {
        String HQL = "SELECT SHOP.SHOP_ID from FLAVOR_SHOP SHOP left join FLAVOR_BUSSINESS_INFO INFO on SHOP.BUSSINESS_ID = INFO.COMPANY_ID where INFO.COMPANY_PROVINCE =?";
        if (StringUtils.isNotBlank(city)) {
            HQL += " AND INFO.COMPANY_CITY = ?";
        }
        if (StringUtils.isNotBlank(country)) {
            HQL += " AND INFO.COMPANY_COUNTY = ?";
        }
        /*final String sql = HQL;
        List<Integer> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List<Integer>>() {
                public List<Integer> doInHibernate(Session session) throws HibernateException,
                                                                   SQLException {
                    Query query = session.createQuery(sql);
                    query.setParameter(0, province);
                    if (StringUtils.isNotBlank(city))
                        query.setParameter(1, city);
                    if (StringUtils.isNotBlank(country))
                        query.setParameter(2, country);
                    return query.list();
                }
            });*/
        Session session = getSession();
        Query query = session.createSQLQuery(HQL);
        query.setParameter(0, province);
        if (StringUtils.isNotBlank(city))
            query.setParameter(1, city);
        if (StringUtils.isNotBlank(country))
            query.setParameter(2, country);
        List<BigInteger> list = (List<BigInteger>) query.list();
        releaseSession(session);

        //类型转换
        List<Integer> result = new ArrayList<Integer>();
        for (BigInteger shopId : list) {
            result.add(shopId.intValue());
        }
        return result;
    }

    public int shopIdGet(String province, String city, String country) {
        final String SQL = "SELECT SHOP.SHOP_ID "
                           + "from FLAVOR_SHOP SHOP left join FLAVOR_BUSSINESS_INFO INFO on SHOP.BUSSINESS_ID = INFO.COMPANY_ID "
                           + "where INFO.COMPANY_PROVINCE =? AND INFO.COMPANY_CITY =? AND INFO.COMPANY_COUNTY = ?";
        int shopId = 0;
        Session session = getSession();
        Query query = session.createSQLQuery(SQL);
        query.setParameter(0, province);
        query.setParameter(1, city);
        query.setParameter(2, country);
        Object obj = query.uniqueResult();
        if (obj != null) {
            shopId = Integer.parseInt(obj.toString());
        }
        releaseSession(session);
        return shopId;
    }

    public List<?> goodsListByShopIdList(final List<Integer> shopIdList, final PageUtil page) {

        if (CollectionUtils.isEmpty(shopIdList))
            return null;

        final String HQL = "from Goods goods where goods.shopID in (:alist) and goods.station = 1 and goods.directSupplyShowType = '1' and goods.show = 1 order by sellOut DESC";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List<?>>() {
            public List<?> doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameterList("alist", shopIdList);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    public List<?> goodsListByShopId(final int shopId, final PageUtil page) {
        final String HQL = "from Goods goods where goods.shopID = ? and goods.station = 1 and goods.show = 1 order by sellOut DESC";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List<?>>() {
            public List<?> doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, shopId);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    public List<?> goodsListByShopId(final int shopId) {
        final String HQL = "from Goods goods where goods.shopID = ? and goods.station = 1 and goods.show = 1 order by sellOut DESC";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List<?>>() {
            public List<?> doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, shopId);
                return query.list();
            }
        });
        return list;
    }

    public List<?> goodsListByType(final int type, final PageUtil page) {
        final String HQL = "from Goods goods where goods.goodsType= ? and goods.station = 1 and goods.show = 1 order by sellOut DESC";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List<?>>() {
            public List<?> doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, type);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    public void compareImg() {
        final String[] TABLE_NAMES = { "flavor_ads", "flavor_ads_bussiness", "flavor_city",
                "flavor_goods", "flavor_goods", "flavor_goods_picture", "flavor_goods_story",
                "flavor_hotcity", "flavor_model_pic", "flavor_model_pic", "flavor_model_pic",
                "flavor_model_pic", "flavor_news", "flavor_order_content", "th_categories",
                "th_group_buying_rules" };
        final String[] COLUMN_NAME = { "PICTURE", "PICTURE", "picture", "GOODS_PICTURE",
                "ACTIVITY_PICTURE", "PICTURE", "STORY_PICTURE", "PICTURE", "PIC_A", "PIC_B",
                "PIC_C", "PIC_D", "PICTURE", "GOODS_PICTURE", "cate_img", "goods_pic" };
        Session session = getSession();
        for (int i = 0; i < TABLE_NAMES.length; i++) {
            String sqlForImgName = String.format("SELECT %s FROM %s", COLUMN_NAME[i],
                TABLE_NAMES[i]);
            final String SQL_FOR_IMG_NAME = sqlForImgName;
            List<?> list = session.createSQLQuery(SQL_FOR_IMG_NAME).list();
            for (Object object : list) {
                try {
                    String[] imgArray = object.toString().split(";");
                    for (String string : imgArray) {
                        if (string.length() > 36) {
                            string = string.substring(string.length() - 36, string.length());
                        } else if (string == null || string.equals("")) {
                            continue;
                        }
                        String sqlForReplace = "UPDATE th_imgs ti SET ti.table_name = ? WHERE ti.path LIKE '%"
                                               + string + "'";
                        final String SQL_FOR_REPLACE = sqlForReplace;
                        Query query = session.createSQLQuery(SQL_FOR_REPLACE);
                        query.setParameter(0, TABLE_NAMES[i]);
                        int columnNumber = query.executeUpdate();
                        System.out.println(columnNumber);

                    }
                } catch (Exception e) {
                    // TODO: handle exception
                    System.out.println(e.getMessage());
                }
            }
        }
        releaseSession(session);

    }

    public boolean updateTopicState(final int topicId, int state) {
        /*
         * state:
         * 0.未发布
         * 1.进行中
         * 2.已结束
         */
        final String SQL = "update th_topics set state = ? where id = ?";
        Session session = getSession();
        Query query = session.createSQLQuery(SQL);
        query.setParameter(0, state);
        query.setParameter(1, topicId);
        int rows = query.executeUpdate();
        if (rows == 1) {
            return true;
        } else {
            return false;
        }
    }

    public List<String> getAllImages(final PageUtil page) {
        final String SQL = "select path from th_imgs";
        List<String> list = getHibernateTemplate().execute(new HibernateCallback<List<String>>() {

            @SuppressWarnings("unchecked")
            public List<String> doInHibernate(Session session) throws HibernateException,
                                                              SQLException {
                // TODO Auto-generated method stub
                Query query = session.createSQLQuery(SQL);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    public List<ThSku> getSkusByGoodsIDs(int[] goodsIDs) {
        String sql = "select * from th_sku where GOODS_ID in (";
        String params = new String();
        for (int i : goodsIDs) {
            params += (i + ",");
        }
        params = params.substring(0, params.length() - 1);
        sql += (params + ")");
        //		query.setParameter(0, params);
        Session session = getSession();
        Query query = session.createSQLQuery(sql).addEntity(ThSku.class);
        List list = query.list();
        ArrayList<ThSku> skuList = new ArrayList<ThSku>();
        for (Object obj : list) {
            skuList.add((ThSku) obj);
        }
        return skuList;
    }

    public List<ThSku> getSkusByGoodsID(String goodID) {
        final String SQL = "select * from th_sku as sku where GOODS_ID = ? order by id";
        Session session = getSession();
        Query query = session.createSQLQuery(SQL).addEntity("sku", ThSku.class);
        query.setParameter(0, goodID);
        LinkedList<ThSku> skuList = new LinkedList<ThSku>();
        for (Object obj : query.list()) {
            skuList.add((ThSku) obj);
        }
        return skuList;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public int getGoodSaleHisAmount(final int goodID) {
        final String SQL = "SELECT SUM(foc.AMOUNT) FROM flavor_order_content foc WHERE GOODS_ID=? AND ACTIVITY_NAME='ThGroupBuyingRules' AND ACTIVITY_SUCCESS='1' ";
        List<String> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, goodID);
                return query.list();
            }
        });
        if (list.size() == 0) {
            return 0;
        } else {
            Object obj = list.get(0);
            if (obj == null) {
                return 0;
            } else {
                return Integer.parseInt(obj.toString());
            }
        }
    }
}
